package com.swn.common.util;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.springframework.jdbc.core.JdbcTemplate;

/**
 * 功能：加载当前数据库用户下表参数工具类
 * 说明：DbTableUtil.map中，key是表名，value是Map<String, Object>。
 * 其中的key代表参数名，value是对应的单表操作的sql或集合：
 * COLNAMES（字段名列表）、PREPARED_PKWHERE（预编译主键查询条件语句 ）、PREPARED_SELECTSQL（默认查询语句）、
 * PREPARED_GETSQL（预编译获取单条数据语句）、PREPARED_REMOVESQL（预编译删除单条数据语句）、COLCODELIST（获取字段列）
 * 
 * 注意：
 *  1.如果不为懒加载，则在监听ContextLoaderListenerImpl的contextInitialized启动；
 *  2.在DbHeper内调用
 *  3.对于不带主键的表不加载
 * @author taozhaocai
 * @Date 2016-5-6  上午10:27:35

 */
public class DbTableUtil {
	private static Logger logger = Logger.getLogger("DbTableUtil.class");
	/** 保存表相关信息的map ***/
	public static Map<String, Map<String, Object>> map = new HashMap<String, Map<String, Object>>();
	/** 是否懒加载 ***/
	public static boolean isLazyLoad = true;
	
	/** 当前数据库名称 ***/
	public static String dbName = "";	
	
	/**
	 * 服务器启动时监听调用的方法：初始化oracle当前用户表参数入口方法
	 * @param whereCondition ：根据查询条件筛选加载的表。 
	 *  void
	 *
	 * @Author taozhaocai
	 * @Date 2016-5-6 上午10:29:29 
	 */
	public static void initTable(String whereCondition) {
		// 清空map,防止多次被调用
		map.clear();
		// 从容器中获取jdbcTemplate
		JdbcTemplate jdbcTemplate = (JdbcTemplate) ServiceLocator.getBean("jdbcTemplate");
		logger.info("开始初始化表信息......");
		//获取数据库名称
		dbName = getDbName(jdbcTemplate);
		logger.info("当前数据库名称："+dbName);
		long startTime = System.currentTimeMillis();
		// oracle所有表名称sql(可根据表名排除掉部分表)
		StringBuffer thisUserTableSql = new StringBuffer();
		thisUserTableSql.append("select table_name from information_schema.tables WHERE TABLE_SCHEMA ='").append(dbName).append("' ");
		if (whereCondition != null && !"".equals(whereCondition)) {
			thisUserTableSql.append("AND ").append(whereCondition);
		}
		//获取需要加载的表名
		List<Map<String, Object>> tableMapList = jdbcTemplate.queryForList(thisUserTableSql.toString());
		for (int i = 0; i < tableMapList.size(); i++) {
			Map<String, Object> map = tableMapList.get(i);
			String tableName = (String) map.get("TABLE_NAME");
			//logger.info("初始化表信息：【"+dbName+"】");
			setTableMap(tableName, jdbcTemplate);
			//logger.info("初始化表信息完毕：【"+dbName+"】");
		}

		long finishTime = System.currentTimeMillis();
		logger.info("初始化表信息完毕，用时秒：" + (finishTime - startTime) / 1000.000);
	}
	
	/**
	 * 根据数据库连接获取数据库名称
	 * 
	 * @param jdbcTemplate
	 * @return String
	 *
	 * @Author 王万兴
	 * @Date 2017年12月14日 下午1:39:06 
	 * @Note <这里是方法的备注>
	 * @company 济南舒卫能生物技术有限公司
	 */
	public static String getDbName(JdbcTemplate jdbcTemplate){
		String dbName = "";
		Connection con  = null;
		try {
		    con = jdbcTemplate.getDataSource().getConnection();
		    
			String url = con.getMetaData().getURL();
			
			if(!"".equals(url)){
				int k =url.lastIndexOf("/") ;
				dbName = url.substring(k+1);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			if(con!=null){
				try {
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		
		return dbName;
	}
	 
	 /**
	  * 设置单表的参数
	  * 
	  * @param tableName
	  * @param jdbcTemplate void
	  *
	  * @Author taozhaocai
	  * @Date 2016-5-6 上午10:30:38 
	  */
	 public static void setTableMap(String tableName,JdbcTemplate jdbcTemplate){
		 if(StringUtil.isEmpty(dbName)){
			 dbName = getDbName(jdbcTemplate);
		 }
		 //String colNames = "select wmsys.wm_concat(column_name) from user_col_comments where table_name=upper('"+tableName+"')";
		 //String keyCol = "SELECT COL.COLUMN_NAME FROM USER_CONSTRAINTS CON,  USER_CONS_COLUMNS COL WHERE CON.CONSTRAINT_NAME = COL.CONSTRAINT_NAME AND CON.CONSTRAINT_TYPE='P' AND COL.TABLE_NAME = '"+tableName+"'";
		 String keyCol = "select COLUMN_NAME from information_schema.COLUMNS where COLUMN_KEY = 'PRI' and table_name = '"+tableName+"' and TABLE_SCHEMA='"+dbName+"'";
			
		 List<Map<String,Object>> keyColMapList = jdbcTemplate.queryForList(keyCol);
		 
		 //如果开始没有执行数据库脚本，然后执行了，则不用重启数据库；不然，需要重启服务
		 if(keyColMapList.size()==0){
			 logger.error("不存在该表："+tableName+"或未设置主键。");
			 return;
		 }
		 
		 /** 所操纵数据库表名称 */
		 String TABLE_NAME = tableName;
		 /** 字段名列表 */
		 //String COLNAMES = jdbcTemplate.queryForObject(colNames, String.class);
		 String COLNAMES = wmConcat(jdbcTemplate,tableName);
		 /** 预编译主键查询条件语句 */
		 String PREPARED_PKWHERE = concatkeyColName(keyColMapList);
		 /** 默认查询语句 */
		 StringBuffer PREPARED_SELECTSQL = new StringBuffer();
		 PREPARED_SELECTSQL.append("SELECT ").append(COLNAMES).append(" FROM ").append(TABLE_NAME);
		 /** 预编译获取单条数据语句 */
		 StringBuffer PREPARED_GETSQL = new StringBuffer();
		 PREPARED_GETSQL.append(PREPARED_SELECTSQL).append(" WHERE ").append(PREPARED_PKWHERE);
		 /** 预编译删除单条数据语句 */
		 StringBuffer PREPARED_REMOVESQL = new StringBuffer();
		 PREPARED_REMOVESQL.append("DELETE FROM ").append(TABLE_NAME).append(" WHERE ").append(PREPARED_PKWHERE);
		 /** 获取字段列 */
		 List<String> COLCODELIST = java.util.Arrays.asList(COLNAMES.split(",")); 
		 Map<String, Object> paramMap = new HashMap<String, Object>();
		 paramMap.put("COLNAMES", COLNAMES);
		 paramMap.put("PREPARED_PKWHERE", PREPARED_PKWHERE);
		 paramMap.put("PREPARED_SELECTSQL", PREPARED_SELECTSQL.toString());
		 paramMap.put("PREPARED_GETSQL", PREPARED_GETSQL.toString());
		 paramMap.put("PREPARED_REMOVESQL", PREPARED_REMOVESQL.toString());
		 paramMap.put("COLCODELIST", COLCODELIST);
		 map.put(TABLE_NAME, paramMap);
	 }
	 
	 /**
	  * 有的数据库不支持wmsys.wm_concat函数，则调用该方法
	  * @param jdbcTemplate
	  * @param tableName
	  * @return
	  */
	 public static String wmConcat(JdbcTemplate jdbcTemplate,String tableName){
		 if(StringUtil.isEmpty(dbName)){
			 dbName = getDbName(jdbcTemplate);
		 }
		 StringBuffer colNames = new StringBuffer("");
		 StringBuffer sql = new StringBuffer();
		 //sql.append("SELECT COLUMN_NAME FROM USER_COL_COMMENTS WHERE TABLE_NAME=UPPER('");
		 sql.append("select COLUMN_NAME from information_schema.COLUMNS where table_name = '");
		 sql.append(tableName).append("' and TABLE_SCHEMA = '").append(dbName).append("'");
		 List<Map<String,Object>> list = jdbcTemplate.queryForList(sql.toString());
		 for (int i = 0; i < list.size(); i++) {
			 Map<String,Object> map = list.get(i);
			 if(i!=list.size()-1){
				 colNames.append(map.get("COLUMN_NAME")).append(",");
			 }else{
				 colNames.append(map.get("COLUMN_NAME"));
			 }
			
		}
		 return colNames.toString();
	 }
	 
	 
	 /**
	  * 表的对象map懒加载公共方法
	  * @param tableName void
	  *
	  * @Author taozhaocai
	  * @Date 2016年8月5日 上午9:36:32
	  */
	 private static void loadTableMap(String tableName){
		 if(!isLazyLoad){
			 return ;
		 }
		 tableName = tableName.toUpperCase();
		 Object obj = map.get(tableName) ;
		 if(obj==null){
			 JdbcTemplate jdbcTemplate	= (JdbcTemplate)ServiceLocator.getBean("jdbcTemplate");
			 logger.info("load table " +tableName);
			 setTableMap(tableName,jdbcTemplate);
		 }
	 }
	 
	 /**
	  * 
	  * 获取某个表的列代码，用逗号间隔
	  * @param tableName
	  * @return String
	  *
	  * @Author taozhaocai
	  * @Date 2016-5-6 上午10:31:09 
	  */
	 public static String COLNAMES(String tableName){
		 if(tableName!=null&&!"".equals(tableName)){
			loadTableMap(tableName); 
			return  (String)(map.get(tableName.toUpperCase())).get("COLNAMES");
		 }
		 return null;
	 }
	 
	 /**
	  * 获取"预编译主键查询条件语句"
	  * 
	  * @param tableName
	  * @return String
	  *
	  * @Author taozhaocai
	  * @Date 2016-5-6 上午10:34:38 
	  */
	 public static String PREPARED_PKWHERE(String tableName){
		 if(tableName!=null&&!"".equals(tableName)){
			loadTableMap(tableName); 
			return  (String)(map.get(tableName.toUpperCase())).get("PREPARED_PKWHERE");
		 }
		 return null;
	 }
	 
	 /**
	  * 获取默认查询语句
	  * 
	  * @param tableName
	  * @return String
	  *
	  * @Author taozhaocai
	  * @Date 2016-5-6 上午11:01:05 
	  */
	 public static String PREPARED_SELECTSQL(String tableName){
		 if(tableName!=null&&!"".equals(tableName)){
			loadTableMap(tableName); 
			return  (String)(map.get(tableName.toUpperCase())).get("PREPARED_SELECTSQL");
		 }
		 return null;
	 }
	 
	 /**
	  * 获取预编译获取单条数据语句 
	  * 
	  * @param tableName
	  * @return String
	  *
	  * @Author taozhaocai
	  * @Date 2016-5-6 上午11:22:10 
	  */
	 public static String PREPARED_GETSQL(String tableName){
		 if(tableName!=null&&!"".equals(tableName)){
			loadTableMap(tableName); 
			return  (String)(map.get(tableName.toUpperCase())).get("PREPARED_GETSQL");
		 }
		 return null;
	 }
	 
	 /**
	  * 获取“预编译删除单条数据语句 ”
	  * 
	  * @param tableName
	  * @return String
	  *
	  * @Author taozhaocai
	  * @Date 2016-5-6 上午11:22:54 
	  */
	 public static String PREPARED_REMOVESQL(String tableName){
		 if(tableName!=null&&!"".equals(tableName)){
			loadTableMap(tableName); 
			return  (String)(map.get(tableName.toUpperCase())).get("PREPARED_REMOVESQL");
		 }
		 return null;
	 }
	 
	 /**
	  * 获取“获取字段列”
	  * 
	  * @param tableName
	  * @return List<String>
	  *
	  * @Author taozhaocai
	  * @Date 2016-5-6 上午11:23:35 
	  */
	 public static List<String> COLCODELIST(String tableName){
		 if(tableName!=null&&!"".equals(tableName)){
			loadTableMap(tableName); 
			return  (List<String>)(map.get(tableName.toUpperCase())).get("COLCODELIST");
		 }
		 return null;
	 }
	 
	 /**
	  * 拼装主键具名参数字符串
	  * 
	  * @param keyColMapList
	  * @return String
	  *
	  * @Author taozhaocai
	  */
	 private static String concatkeyColName(List<Map<String,Object>> keyColMapList){
		 StringBuffer concatkeyCol = new StringBuffer();
		 for (int i = 0; i < keyColMapList.size(); i++) {
			 Map<String,Object> map = keyColMapList.get(i);
			 String key = (String)map.get("column_name");
			 if(i==keyColMapList.size()-1){
				 concatkeyCol.append(key).append("=:").append(key);
			 }else{
				 concatkeyCol.append(key).append("=:").append(key).append(" and ");
			 }
		}
		 return concatkeyCol.toString();
	 }
	 
	 public static void main(String[] args) {
		 Map<String,Object> map = new HashMap<String,Object>();
		 System.out.println(map.get("ddd"));
	}

}
